package coder.nav.schedule.action;

import cn.hutool.core.date.DateUtil;
import coder.nav.model.bo.NavTaskActionBO;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.time.DateUtils;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@Slf4j
@Component
public class NavDataExportAction extends NavTaskNoticeAction {

    @Value("${spring.datasource.url}")
    private String JDBC_URL;

    @Value("${spring.datasource.username}")
    private String USERNAME;

    @Value("${spring.datasource.password}")
    private String PASSWORD;

    @Override
    public String title() {
        return "数据备份";
    }

    @Override
    public String context() {
        return "";
    }

    @Override
    public String params() {
        return super.params();
    }

    @Override
    public Date executeTime() {
        return DateUtils.addMinutes(DateUtils.addHours(DateUtil.beginOfDay(new Date()), 0), 0);
    }

    @Override
    public NavTaskActionBO getEntity(Long userId) {
        NavTaskActionBO bo = super.getEntity(userId);
        bo.setNeedToHtml(false);
        return bo;
    }

    @Override
    public void addTask(Long userId) {
        NavTaskActionBO task = this.getEntity(userId);

        Date date = new Date();
        Integer year = DateUtil.year(date);
        Integer month = DateUtil.month(date) + 1;
        Integer day = DateUtil.dayOfMonth(date);

        task.setTitle(String.format("%s-%s年%s月%s日", task.getTitle(), year, month, day));

        try (Connection connection = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD)) {
            // 获取所有表名
            List<String> tableNames = showTables(connection);
            // 导出所有表的结构和数据
            StringBuilder context = new StringBuilder();
            for (String tableName : tableNames) {

                // 导出表结构
                context.append("-- show create table ").append(tableName).append("\n\n");
                String createTableSQL = showCreateTable(connection, tableName);
                context.append(createTableSQL).append("\n\n");

                // 导出表数据
                context.append("-- select * from ").append(tableName).append("\n\n");
                List<String> insertSQLs = selectFrom(connection, tableName);
                for (String insertSQL : insertSQLs) {
                    context.append(insertSQL).append("\n");
                }
                context.append("\n");
            }
            task.setContext(context.toString());
        } catch (Exception ex) {
            task.setTitle(task.getTitle() + "-数据备份失败");
            task.setContext(ex.getMessage());
        }
        super.taskService.saveTaskAction(task);
    }

    @Override
    public void actionTask(NavTaskActionBO task) {
        super.actionTask(task, 0, "sql");
    }

    /**
     * show tables
     */
    private List<String> showTables(Connection connection) throws SQLException {
        List<String> tableNames = new ArrayList<>();
        DatabaseMetaData metaData = connection.getMetaData();
        ResultSet resultSet = metaData.getTables(null, null, null, new String[]{"TABLE"});

        while (resultSet.next()) {
            String tableName = resultSet.getString("TABLE_NAME");
            if(tableName.contains("sys_")) {
                continue;
            }
            tableNames.add(tableName);
        }
        return tableNames;
    }

    /**
     * show create table
     */
    public static String showCreateTable(Connection connection, String tableName) throws SQLException {
        ResultSet resultSet = connection.createStatement().executeQuery("SHOW CREATE TABLE " + tableName);
        while(resultSet.next()) {
            return resultSet.getString("Create Table") + ";";
        }
        return null;
    }

    /**
     * select * from
     */
    public static List<String> selectFrom(Connection connection, String tableName) throws SQLException {
        List<String> insertSQLs = new ArrayList<>();
        String query = "SELECT * FROM " + tableName;

        try (Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(query)) {

            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            while (resultSet.next()) {
                StringBuilder insertSQL = new StringBuilder("INSERT INTO " + tableName + " VALUES (");
                for (int i = 1; i <= columnCount; i++) {
                    String columnType = metaData.getColumnTypeName(i);
                    Object value = resultSet.getObject(i);

                    // 处理 DATETIME 类型
                    if ("DATETIME".equalsIgnoreCase(columnType) && value != null) {
                        Timestamp timestamp = resultSet.getTimestamp(i);
                        insertSQL.append("'").append(timestamp.toString()).append("'");
                    }
                    // 处理字符串类型（包括富文本）
                    else if (value instanceof String) {
                        String escapedValue = escapeString((String) value); // 转义特殊字符
                        insertSQL.append("'").append(escapedValue).append("'");
                    }
                    // 处理其他类型
                    else if (value != null) {
                        insertSQL.append(value);
                    } else {
                        insertSQL.append("NULL"); // 处理 NULL 值
                    }

                    if (i < columnCount) {
                        insertSQL.append(", ");
                    }
                }
                insertSQL.append(");");
                insertSQLs.add(insertSQL.toString());
            }
        }

        return insertSQLs;
    }

    // 转义字符串中的特殊字符
    private static String escapeString(String value) {
        if (value == null) {
            return null;
        }
        // 转义单引号
        return value.replace("'", "''");
    }

}
